import re,sys,os
import re
import glob
from PySide6.QtGui import *
from PySide6.QtWidgets import *
from PySide6.QtCore import *
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.utils import column_index_from_string
import win32com.client as win32
import warnings
# 忽略UserWarning警告
warnings.filterwarnings("ignore", category=UserWarning)


sheet_title_num = {
    "BZR":7,
    "MID":7,
    "MSG":7,
    "Variable_MID":7,
    "Variable_MSG":7,
    "SW":9,
    "SW_MSG":8
}

sheet_dis_str = {
    "TT":"【XXX】途絶判定\n前提：+B ON、IG ON\nNo.xx\nTT消灯——途絶中——TT消灯は続けて(xxs内)——途絶後（xxs後）——TT消灯/点灯 OK\n途絶—復帰—TT消灯 OK \nNO.xx\nTT点灯中——途絶中——TT点灯は続けて(xxs内)——途絶後（xxs後）——TT点灯/消灯 OK\n途絶—復帰—TT点灯 OK \n前提：+B ON、IG OFF\nIG-OFF非点灯/IG-ONと同じです OK",
    "BZR":"【XXX】途絶判定\n前提：+B ON、IG ON\nNo.xx\nBUZZER吹鳴しない→途絶中BUZZER吹鳴しない（xxs内）→xxs後BUZZER吹鳴しない/ BUZZER吹鳴    OK\n途絶→復帰→BUZZER吹鳴しない       OK\n途絶判定時間:xx秒経過     OK \nNo.2\nBUZZER吹鳴→途絶中BUZZER吹鳴（xxs内）→xxs（手動計時）後BUZZER吹鳴しない/ BUZZER吹鳴          OK\n途絶→復帰→BUZZER吹鳴     OK \n途絶判定時間:xx秒経過       OK\n前提：+B ON、IG OFF\nIG-OFF非吹鳴/IG-ONと同じです OK",
    "MID":"【XXX】途絶判定\n前提：+B ON、IG ON\nNo.xx\nMID非表示——途絶中——MID非表示は続けて(xxs内)——途絶後（xxs後）——MID非表示/MID表示   OK\n途絶—復帰—MID非表示/MID表示 OK \nNO.xx\n表示中——途絶中——MID表示は続けて(xxs内)——途絶後（xxs後）——MID表示/MID非表示  OK\n途絶—復帰—MID表示 OK \n前提：+B ON、IG OFF\nIG-OFF非表示/ IG-ONと同じです OK",
    "Variable_MID":"【XXX】途絶判定\n前提：+B ON、IG ON\nNo.xx\nMID非表示——途絶中——MID非表示は続けて(xxs内)——途絶後（xxs後）——MID非表示/MID表示   OK\n途絶—復帰—MID非表示/MID表示 OK \nNO.xx\n表示中——途絶中——MID表示は続けて(xxs内)——途絶後（xxs後）——MID表示/MID非表示  OK\n途絶—復帰—MID表示 OK \n前提：+B ON、IG OFF\nIG-OFF非表示/ IG-ONと同じです OK",
    "SW":"途絶判定\n前提：+B ON、IG ON\n機能有\n①xx表示中——途絶中——xx表示は続けて(xxs内)——途絶後——表示は続けて、「xx」設定状態：ブランク(タイミング:xx s)         OK\n途絶—復帰—ON表示は続けて          OK \n②xxx\n機能無\n③非表示——途絶中——非表示は続けて(xxs内)——途絶後——非表示は続けて(xxs後)         OK\n途絶—復帰—非表示は続けて          OK \n前提：+B ON、IG OFF\n非表示 OK"
}

sheet_priority_seq = {
    "TT":"手順：\n”XX”出力確認：　　\nテスト観点：-\nテスト手順：\n1:XX\n2:XX\n→出力值 Fream：XX  Singer:XX=XX 　テスト結果:OK/NG(MD No.XX)\n”XX”出力しない確認：\nENG1G90  RDYIND = 0\nIPA1S05  APP_EXT=1b and APPME = 0011b  =>  MET1S36 APPW=0   OK\n\nENG1G90  RDYIND = 1\nIPA1S05   APPME = 01b  OR 00b  =>  MET1S36 APPW=0   OK\n 参考R/W仕様「MET-M_REMWAR-XX」を参照する",
    "BZR":"■低優先順：\n高優先：現在のBZR_No.xx吹鳴中\n低優先：No.xx 信号 = xxb 割入\n期待値：低優先未割入 、 高優先：現在のBZR_No.xx吹鳴  テスト結果：OK\n■高優先順：\n低優先：現在のBZR_No.xx吹鳴中\n高優先：No.xx 信号= xxb割入\n期待値： 高優先割入 、高優先：NO.xx吹鳴     テスト結果：OK",
    "MID":"■低優先順：\n高優先：現在のMID_No.xx表示中\n低優先：No.xx 信号 = xxb 割入\n期待値：低優先未割入 、 高優先：現在のMID_No.xx表示続けて  テスト結果：OK\n■高優先順：\n低優先：現在のMID_No.xx表示中\n高優先：No.xx 信号 = xxbb  割入\n期待値： 高優先割入 、高優先：NO.xx表示     テスト結果：OK",
    "Variable_MID":"■低優先順：\n高優先：現在のMID_No.xx表示中\n低優先：No.xx 信号 = xxb 割入\n期待値：低優先未割入 、 高優先：現在のMID_No.xx表示続けて  テスト結果：OK\n■高優先順：\n低優先：現在のMID_No.xx表示中\n高優先：No.xx 信号 = xxbb  割入\n期待値： 高優先割入 、高優先：NO.xx表示     テスト結果：OK"
}

sheet_info_type = {
    "MID":"NO.xx\n■(Ⅰ/Ⅱ/ⅢR/ⅢUR)Warning/Caution/Advisory/ACC/LKA\n初回表示：OK\n循環表示：OK\nCancel後継続表示/非表示：OK\n\nメッセージタブ表示対象：○/X \n表示/非表示  OK",
    "Variable_MID":"NO.xx\n■(Ⅰ/Ⅱ/ⅢR/ⅢUR)Warning/Caution/Advisory/ACC/LKA\n初回表示：OK\n循環表示：OK\nCancel後継続表示/非表示：OK\n\nメッセージタブ表示対象：○/X \n表示/非表示  OK"
}

def app_path():
    if hasattr(sys, 'frozen'):  
        return os.path.abspath(sys.executable)
    return os.path.abspath(__file__) 

class OpenFolderWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.edit = QLineEdit()
        self.button_open = QPushButton('open folder')
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.edit)
        self.layout.addWidget(self.button_open)
        self.edit.setReadOnly(True)

class ProgressBarWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.button_start = QPushButton('start')
        self.progress_bar = QProgressBar()
        self.progress_bar.setValue(0)
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.progress_bar)
        self.layout.addWidget(self.button_start)

class CodeCheckView(QWidget):
    signal_folder = Signal(str)
    signal_excelfile = Signal(str)
    signal_currentfile = Signal(str)
    def __init__(self):
        super().__init__()
        self.gtr_workbook_events = None
        self.code_path = OpenFolderWidget('File path:   ')
        self.progress = ProgressBarWidget('Progress bar:')
        input_layout = QVBoxLayout()
        input_layout.addWidget(self.code_path)
        input_layout.addWidget(self.progress)
        group_box_input = QGroupBox('')
        group_box_input.setLayout(input_layout)
        tab_layout = QVBoxLayout()
        tab_layout.addWidget(group_box_input)
        self.setLayout(tab_layout)
        self.setWindowTitle("Mask Coverage")
        self.resize(600, 200)
        self.show()

        self.code_path.button_open.clicked.connect(self.SlotChooseFolder)
        self.progress.button_start.clicked.connect(self.TraversalFolder)

    def ProgressBar(self, check_process): 
        self.progress.progress_bar.setValue(check_process)
        QCoreApplication.processEvents()

    def SlotChooseFolder(self): # open folder
        folder_path = QFileDialog.getExistingDirectory(self)
        self.code_path.edit.setText(folder_path)
        self.signal_folder = folder_path
    
    def SlotChooseExcelFile(self): # open excel
        file_path = QFileDialog.getOpenFileName(self)
        self.excel_path.edit.setText(file_path[0])
        self.signal_excelfile = file_path[0]

    def TraversalFolder(self): # start
        folder_path = self.signal_folder
        for filename in glob.glob(f'{folder_path}/**/*.xlsm',recursive=True):
            print(filename)
            if r"\~$" not in filename:
                Mask_Coverage(filename)
            self.ProgressBar((glob.glob(f'{folder_path}/**/*.xlsm',recursive=True).index(filename)+1)/len(glob.glob(f'{folder_path}/**/*.xlsm',recursive=True)) * 100)
        QMessageBox.information(None, 'result', 'Excel file mask coverage completed.           ')

def insert_rectangles_to_sheets(file_name, mask_coverage_info):
    # 启动Excel应用
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False  # 不显示Excel界面
    try:
        # 打开工作簿
        wb = excel.Workbooks.Open(file_name)
        # 遍历所有工作表
        for sheet in wb.Sheets:
            # print(sheet.Name)
            if sheet.Name in mask_coverage_info:
                sheet.Activate()  # 确保目标 Sheet 是活动状态
                excel.ActiveWindow.Zoom = 100  # 设置当前活动窗口的缩放比例,强制100%显示
                for key,values in mask_coverage_info[sheet.Name].items():
                    # 获取开始和结束单元格，矩形填充色
                    full_range = sheet.Range(key)
                    # 计算位置和尺寸
                    left = full_range.Left
                    top = full_range.Top
                    width = full_range.Width
                    height = full_range.Height
                    # 微调参数（根据实际效果调整）
                    offset = 0
                    # 添加矩形
                    shape = sheet.Shapes.AddShape(
                        1,  # msoShapeRectangle
                        left - offset,
                        top - offset,
                        width + 2 * offset,
                        height + 2 * offset
                    )
                    if values["color"] == "blue":
                        ForeColor = 0xFFFF66
                    elif values["color"] == "green":
                        ForeColor = 0x33FF66
                    elif values["color"] == "yellow":
                        ForeColor = 0x00FFFF
                    else:
                        ForeColor = 0x00FFFF
                    # 设置形状格式
                    shape.Fill.ForeColor.RGB = ForeColor  # 蓝色填充
                    shape.Fill.Transparency = 0.5  # 设置透明度为50%
                    shape.Line.ForeColor.RGB = 0x000000  # 黑色边框
                    # shape.Line.Visible = False  # 隐藏矩形边框
                    # 可选：锁定位置随单元格变化
                    shape.Placement = 3  # xlMoveAndSize
                    # 添加并设置文本
                    shape.TextFrame.Characters().Text = values["text"]
                    shape.TextFrame.HorizontalAlignment = 1  # 左对齐(1=xlLeft)
                    shape.TextFrame.VerticalAlignment = 1    # 顶部对齐(1=xlTop)
                    # 设置文本格式
                    text_range = shape.TextFrame.Characters()
                    text_range.Font.Color = 0x000000  # 黑色文本
                    text_range.Font.Size = 18         # 11号字
                    text_range.Font.Name = "MS Pゴシック"     # 日文字体
                    text_range.Font.Bold = True       # 设置文本为加粗
                    # 设置文本边距
                    shape.TextFrame.MarginLeft = 5
                    shape.TextFrame.MarginRight = 5
                    shape.TextFrame.MarginTop = 5
                    shape.TextFrame.MarginBottom = 5
                    # 自动调整文本换行
                    shape.TextFrame2.WordWrap = True
                # 保存工作簿
                wb.Save()
        print(f"处理完成，结果已保存到: {file_name}")
    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        QMessageBox.information(None, 'Exception', f'处理[{file_name}]过程中发生错误[{str(e)}].           ')
    finally:
        # 确保Excel进程被关闭
        wb.Close(False)
        excel.Quit()

def cell_row_process(sheet, row, min_col, max_col, data_value, values):
    for c_row in sheet.iter_rows(min_row=row, max_row=row, min_col=min_col, max_col=max_col):
        for cell in c_row:
            cell_meger = 0
            meger_col = 0
            for merged_range in sheet.merged_cells.ranges:
                if cell.coordinate in merged_range:
                    cell_meger = 1
                    if cell.coordinate == merged_range.start_cell.coordinate:
                        meger_col = merged_range.max_col
            if cell_meger != 0:
                if meger_col != 0:
                    data_value[f"{get_column_letter(cell.column)}{cell.row}:{get_column_letter(meger_col)}{cell.row}"] = dict(values)
            else:
                data_value[f"{get_column_letter(cell.column)}{cell.row}:{get_column_letter(cell.column)}{cell.row}"] = dict(values)

def col_end_process(sheet, row):
    col_end = 0
    for cell in row:
        if cell.border:
            if cell.border.right is not None and cell.border.right.style is not None:
                col_end = cell.column
                for merged_range in sheet.merged_cells.ranges:
                    if cell.coordinate in merged_range:
                        col_end = merged_range.max_col
    return col_end

def get_real_max_row(sheet):
    """
    逆向遍历检查 Sheet，返回实际包含数据的最大行号
    :param sheet: openpyxl 的 Worksheet 对象
    :return: 最大数据行号（全空表返回0）
    """
    # 从最大行开始逆向检查（openpyxl行号从1开始）
    for row_idx in range(sheet.max_row, 0, -1):
        # 检查当前行所有列是否有非空值
        for col_idx in range(1, sheet.max_column + 1):
            cell_value = sheet.cell(row=row_idx, column=col_idx).value
            # 如果单元格非空（None/空字符串/空白字符均视为空）
            if cell_value is not None and str(cell_value).strip():
                return row_idx
    return 0

def check_excel(file_name):
    try:
        # 打开Excel文件
        wb = openpyxl.load_workbook(file_name, data_only=True)
        # 存储每个sheet页的数据
        sheets_data = {}
        # 遍历每个sheet
        for sheet in wb.worksheets:
            if sheet.sheet_state != 'hidden' :  # 检查sheet是否隐藏
                sheet_name = str(sheet.title).strip()
                max_row = sheet.max_row
                max_column = sheet.max_column
                # print(f"sheet_name={sheet_name} max_row={max_row} max_column={get_column_letter(max_column)}")
                data_value = {}
                values = {}
                if sheet_name == "Revision history":
                    values["color"] = "green"
                    values["text"] = "テスト項目なし、検査対象外ソフト制御に影響無いため対象外"
                    data_value[f"A1:{get_column_letter(max_column)}{max_row}"] = dict(values)
                else:
                    min_row = 2
                    min_col = 4
                    max_row = get_real_max_row(sheet)
                    col_flag = 0
                    for row in sheet.iter_rows(min_row=min_row, max_row=min_row, min_col=1, max_col=max_column):
                        for tmp_cell in row:
                            if str(tmp_cell.value).strip() == "メータDB用エリア":
                                col_flag = col_flag + 1
                                min_col = tmp_cell.column + 2
                            if str(tmp_cell.value).strip() == "メータDB用，拡張用エリア":
                                col_flag = col_flag + 1
                                max_column = tmp_cell.column - 1
                    if col_flag != 2:
                        QMessageBox.information(None, 'Exception', f'处理[{file_name}]过程中[{sheet_name}]第二行没有找到[メータDB用エリア/メータDB用，拡張用エリア]数据.           ')
                        return sheets_data
                    # print(f"sheet_name={sheet_name} max_row={max_row} min_col={get_column_letter(min_col)} max_column={get_column_letter(max_column)}")
                    tmp_end_cell = str(sheet[f"A{max_row}"].value).strip()
                    if tmp_end_cell == "end":
                        max_row = max_row - 1
                    else:
                        max_row = max_row + 1
                    start_cell = f"{get_column_letter(min_col)}{min_row}"
                    if sheet_name != "TT":
                        values["color"] = "green"
                        values["text"] = "概要のため対象外、テスト項目なし、検査対象外"
                        if sheet_name in sheet_title_num:
                            # 表格title部分处理
                            row_tmp = min_row + sheet_title_num[sheet_name]
                            col_end = 0
                            for row in sheet.iter_rows(min_row=row_tmp, max_row=row_tmp, min_col=min_col, max_col=max_column):
                                col_end = col_end_process(sheet, row)
                            data_value[f"{start_cell}:{get_column_letter(col_end)}{row_tmp}"] = dict(values)
                            row_tmp = row_tmp + 1
                            start_cell = f"{get_column_letter(min_col)}{row_tmp}"
                            # 表格数据部分处理
                            values["text"] = ""
                            if sheet_name == "MSG" or sheet_name == "Variable_MSG":
                                max_row = max_row - 1
                                data_value[f"{start_cell}:{get_column_letter(min_col)}{max_row}"] = dict(values)
                                for cell in sheet["E"]:
                                    if max_row >= cell.row >= row_tmp:
                                        if str(cell.value).strip() != "None":
                                            values["color"] = "blue"
                                            cell_row_process(sheet, cell.row, cell.column, max_column, data_value, values)
                                        else:
                                            values["color"] = "green"
                                            data_value[f"{get_column_letter(cell.column)}{cell.row}:{get_column_letter(max_column)}{cell.row}"] = dict(values)
                            if sheet_name == "SW_MSG":
                                max_row = max_row - 1
                                data_value[f"{start_cell}:{get_column_letter(min_col)}{max_row}"] = dict(values)
                                data_value[f"H{row_tmp}:{get_column_letter(max_column)}{max_row}"] = dict(values)
                                for row in sheet.iter_rows(min_row=row_tmp, max_row=max_row, min_col=column_index_from_string("E"), max_col=column_index_from_string("G")):
                                    # 遍历行中的所有单元格
                                    for cell in row:
                                        if cell.value is not None:
                                            values["color"] = "blue"
                                        else:
                                            values["color"] = "green"
                                        data_value[f"{get_column_letter(cell.column)}{cell.row}:{get_column_letter(cell.column)}{cell.row}"] = dict(values)
                            if sheet_name == "BZR" or sheet_name == "MID" or sheet_name == "Variable_MID":
                                mid_row = 0
                                v_mid_row = 0
                                yjc_row = 0
                                col_end = 0
                                for row in sheet.iter_rows(min_row=row_tmp-2, max_row=row_tmp-2, min_col=min_col, max_col=max_column):
                                    col_end = col_end_process(sheet, row)
                                if sheet_name == "Variable_MID":
                                    if str(sheet[f"F{row_tmp-1}"].value).strip() == "意匠例":
                                        yjc_row = row_tmp
                                for row in sheet.iter_rows(min_row=row_tmp, max_row=max_row, min_col=column_index_from_string("B"), max_col=max_column):
                                    cell_row = row[0].row
                                    # 遍历行中的所有单元格
                                    if sheet_name == "MID" and str(row[2].value).strip() == "4-4.積算値・学習値の固定MID内での表示有無とバッテリ再接続後の値リセット有無":
                                        mid_row = cell_row + 2
                                    if sheet_name == "Variable_MID" and str(row[2].value).strip() == "5-4.積算値・学習値の可変MID内での表示有無とバッテリ再接続後の値リセット有無":
                                        v_mid_row = cell_row + 2
                                    if sheet_name == "Variable_MID" and str(row[4].value).strip() == "意匠例":
                                        yjc_row = cell_row + 1
                                    if str(row[2].value).strip() != "None":
                                        if str(row[2].value).isdigit() and mid_row == 0 and v_mid_row == 0 and yjc_row == 0:
                                            if str(row[4].value).strip() != "None":
                                                values["color"] = "blue"
                                                cell_row_process(sheet, cell_row, row[4].column, col_end, data_value, values)
                                            else:
                                                values["color"] = "green"
                                                data_value[f"{get_column_letter(row[4].column)}{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                        if start_cell == "":
                                            start_cell = f"{get_column_letter(row[2].column)}{cell_row}"
                                    else:
                                        if all(cell.value is None for cell in row):
                                            values["color"] = "green"
                                            if v_mid_row != 0:
                                                data_value[f"{start_cell}:{get_column_letter(row[2].column)}{cell_row-1}"] = dict(values)
                                            elif mid_row != 0:
                                                pass
                                            else:
                                                data_value[f"{start_cell}:{get_column_letter(row[3].column)}{cell_row-1}"] = dict(values)
                                            start_cell = ""
                                            mid_row = 0
                                            v_mid_row = 0
                                            yjc_row = 0
                                    if str(row[0].value).strip() == "要求ID":
                                        col_end = col_end_process(sheet, row)
                                        values["color"] = "green"
                                        if str(sheet[f"{get_column_letter(row[0].column)}{cell_row+1}"].value).strip() == "None":
                                            data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row+1}"] = dict(values)
                                        else:
                                            data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                        start_cell = ""
                                        for tmpcell in row:
                                            if "途絶判定時間" in str(tmpcell.value).strip():
                                                values["color"] = "blue"
                                                values["text"] = sheet_dis_str[sheet_name]
                                                data_value[f"{get_column_letter(col_end+1)}{cell_row}:{get_column_letter(col_end+5)}{cell_row+9}"] = dict(values)
                                                values["text"] = ""

                                    if mid_row != 0 and mid_row <= cell_row:
                                        values["color"] = "yellow"
                                        data_value[f"D{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                    if v_mid_row != 0 and v_mid_row <= cell_row:
                                        if str(row[3].value).strip() != "None":
                                            values["color"] = "blue"
                                            cell_row_process(sheet, cell_row, row[3].column, col_end, data_value, values)
                                        else:
                                            values["color"] = "green"
                                            data_value[f"E{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                    if yjc_row != 0 and yjc_row <= cell_row:
                                        if str(row[4].value).strip() != "None":
                                            values["color"] = "blue"
                                            cell_row_process(sheet, cell_row, row[4].column, col_end, data_value, values)
                                        else:
                                            values["color"] = "green"
                                            data_value[f"F{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                            if sheet_name == "SW":
                                yjc_row = 0
                                bszl_row = 0
                                col_end = 0
                                for row in sheet.iter_rows(min_row=row_tmp-2, max_row=row_tmp-2, min_col=min_col, max_col=max_column):
                                    col_end = col_end_process(sheet, row)
                                if str(sheet[f"F{row_tmp-2}"].value).strip() == "シンボル/意匠例":
                                    yjc_row = row_tmp
                                if str(sheet[f"F{row_tmp-2}"].value).strip() == "表示\n種類":
                                    bszl_row = row_tmp
                                for row in sheet.iter_rows(min_row=row_tmp, max_row=max_row, min_col=column_index_from_string("B"), max_col=max_column):
                                    cell_row = row[0].row
                                    # 遍历行中的所有单元格
                                    if str(row[2].value).strip() != "None":
                                        if str(row[2].value).isdigit() and yjc_row == 0 and bszl_row == 0:
                                            if str(row[4].value).strip() != "None":
                                                values["color"] = "blue"
                                                cell_row_process(sheet, cell_row, row[4].column, col_end, data_value, values)
                                            else:
                                                values["color"] = "green"
                                                data_value[f"{get_column_letter(row[4].column)}{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                        if start_cell == "":
                                            start_cell = f"{get_column_letter(row[2].column)}{cell_row}"
                                    else:
                                        end_str = str(sheet[f"{get_column_letter(row[2].column)}{cell_row-1}"].value).strip()
                                        result = re.fullmatch(r'^S\d$', end_str)
                                        if end_str.isdigit() or result != None:
                                            values["color"] = "green"
                                            if bszl_row != 0:
                                                data_value[f"{start_cell}:{get_column_letter(row[4].column)}{cell_row-1}"] = dict(values)
                                            else:
                                                data_value[f"{start_cell}:{get_column_letter(row[3].column)}{cell_row-1}"] = dict(values)
                                            start_cell = ""
                                            yjc_row = 0
                                            bszl_row = 0
                                    if str(row[0].value).strip() == "要求ID":
                                        col_end = col_end_process(sheet, row)
                                        values["color"] = "green"
                                        if str(sheet[f"{get_column_letter(row[0].column)}{cell_row+1}"].value).strip() == "None":
                                            data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row+1}"] = dict(values)
                                        else:
                                            data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                        start_cell = ""
                                        for tmpcell in row:
                                            if "途絶判定時間" in str(tmpcell.value).strip():
                                                values["color"] = "blue"
                                                values["text"] = sheet_dis_str[sheet_name]
                                                data_value[f"{get_column_letter(col_end+1)}{cell_row}:{get_column_letter(col_end+5)}{cell_row+9}"] = dict(values)
                                                values["text"] = ""

                                    if str(row[4].value).strip() == "シンボル/意匠例":
                                        yjc_row = cell_row + 2
                                    if str(row[4].value).strip() == "表示\n種類":
                                        bszl_row = cell_row + 2
                                    if yjc_row != 0 and yjc_row <= cell_row:
                                        if str(row[8].value).strip() != "None":
                                            values["color"] = "blue"
                                            cell_row_process(sheet, cell_row, row[4].column, col_end, data_value, values)
                                        else:
                                            values["color"] = "green"
                                            data_value[f"F{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                    if bszl_row != 0 and bszl_row <= cell_row:
                                        if str(row[5].value).strip() != "None" or str(row[4].value).strip() == "画像":
                                            values["color"] = "blue"
                                            cell_row_process(sheet, cell_row, row[5].column, col_end, data_value, values)
                                        else:
                                            values["color"] = "green"
                                            data_value[f"F{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                        else:
                            continue
                    else:
                        col_end = 0
                        for cell in sheet["D"]:
                            cell_value = str(cell.value)
                            if cell_value == "R/W送信信号の有無":
                                for row in sheet.iter_rows(min_row=cell.row, max_row=cell.row, min_col=min_col, max_col=max_column):
                                    col_end = col_end_process(sheet, row)
                                values["color"] = "green"
                                values["text"] = "概要のため対象外、テスト項目なし、検査対象外"
                                data_value[f"{start_cell}:{get_column_letter(col_end)}{cell.row - 1}"] = dict(values)
                                values["color"] = "blue"
                                values["text"] = "OK               R/W仕様「MET-M_REMWAR-CSTD-1-13-A-C0.xlsx」を参照する::MSP::18"
                                data_value[f"{get_column_letter(min_col)}{cell.row}:{get_column_letter(col_end)}{cell.row}"] = dict(values)
                                values["color"] = "blue"
                                values["text"] = sheet_priority_seq[sheet_name]
                                data_value[f"{get_column_letter(col_end+1)}{cell.row}:{get_column_letter(col_end+5)}{cell.row+9}"] = dict(values)
                                start_cell = f"{get_column_letter(min_col)}{cell.row + 1}"
                            elif cell_value == "2.テルテール(以下TT，インジケータと同義)":
                                values["color"] = "green"
                                values["text"] = "概要のため対象外、テスト項目なし、検査対象外"
                                data_value[f"{start_cell}:{get_column_letter(col_end)}{cell.row - 1}"] = dict(values)
                                start_cell = f"{get_column_letter(min_col)}{cell.row}"
                                min_row = cell.row
                                break
                        yjc_row = 0
                        values["text"] = ""
                        # for row in sheet.iter_rows(min_row=1, max_row=max_row, min_col=1, max_col=max_column, values_only=True):
                        for row in sheet.iter_rows(min_row=min_row, max_row=max_row, min_col=column_index_from_string("B"), max_col=max_column):
                            cell_row = row[0].row
                            # 遍历行中的所有单元格
                            if str(row[2].value).strip() == "シンボル":
                                yjc_row = cell_row + 1
                            if str(row[2].value).strip() != "None":
                                if str(row[2].value).isdigit() and yjc_row == 0:
                                    if str(row[4].value).strip() != "None":
                                        values["color"] = "blue"
                                        cell_row_process(sheet, cell_row, row[4].column, col_end, data_value, values)
                                    else:
                                        values["color"] = "green"
                                        data_value[f"{get_column_letter(row[4].column)}{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                if start_cell == "":
                                    start_cell = f"{get_column_letter(row[2].column)}{cell_row}"
                            else:
                                if all(cell.value is None for cell in row):
                                    values["color"] = "green"
                                    if yjc_row != 0 and str(sheet[f"{get_column_letter(row[2].column)}{cell_row-1}"].value).strip() == "None":
                                        pass
                                    else:
                                        data_value[f"{start_cell}:{get_column_letter(row[3].column)}{cell_row-1}"] = dict(values)
                                    start_cell = ""
                                    yjc_row = 0
                            if str(row[0].value).strip() == "要求ID":
                                col_end = col_end_process(sheet, row)
                                values["color"] = "green"
                                if str(sheet[f"{get_column_letter(row[0].column)}{cell_row+1}"].value).strip() == "None":
                                    data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row+1}"] = dict(values)
                                else:
                                    data_value[f"{start_cell}:{get_column_letter(col_end)}{cell_row}"] = dict(values)
                                start_cell = ""
                                for tmpcell in row:
                                    if "途絶判定時間" in str(tmpcell.value).strip():
                                        values["color"] = "blue"
                                        values["text"] = sheet_dis_str[sheet_name]
                                        data_value[f"{get_column_letter(col_end+1)}{cell_row}:{get_column_letter(col_end+5)}{cell_row+9}"] = dict(values)
                                        values["text"] = ""

                            if yjc_row != 0 and yjc_row <= cell_row:
                                if str(row[6].value).strip() != "None":
                                    values["color"] = "blue"
                                    cell_row_process(sheet, cell_row, row[2].column, col_end, data_value, values)
                                else:
                                    values["color"] = "green"
                                    data_value[f"D{cell_row}:{get_column_letter(col_end)}{cell_row}"] = dict(values)

                # print(data_value)
                # 保存数据到字典中
                sheets_data[sheet_name] = data_value
        # print(sheets_data)
        # print(sheets_data["Variable_MID"])
        return sheets_data
    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        QMessageBox.information(None, 'Exception', f'处理[{file_name}]过程中发生错误[{str(e)}].           ')
        
def Mask_Coverage(file_name):
    # 获取Excel文件中需要填充的区域及数据
    mask_coverage_info = check_excel(file_name)

    # 根据覆盖信息对文件单元格进行蒙版覆盖
    if mask_coverage_info:
        insert_rectangles_to_sheets(file_name, mask_coverage_info)


if __name__ == "__main__":
    # if os.path.exists(error_file):
    #     os.remove(error_file)
    # data = [["CPP file path", "Error description"]]
    # # print(f"{data}")
    # export_to_csv(data,error_file)

    app = QApplication([])
    window = CodeCheckView()
    app.exec()